
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Calendar;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.TreeMap;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.hssf.util.HSSFColor.HSSFColorPredefined;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.CellValue;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Font;
import org.apache.poi.ss.usermodel.FormulaEvaluator;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

public class ExcelReaderAndWriter {

	public Logger logger = LoggerFactory.getLogger(getClass());

	private Map<String, Map<String, String[]>> map = new LinkedHashMap<String, Map<String, String[]>>();

	private Map<String, String> dateWeekMap = new TreeMap<String, String>();
	
	private int daysOfMonth = 0;
	
	private CellStyle styleTitle;
	private CellStyle styleLate;
	private CellStyle styleKuang;
	private CellStyle styleCommon;
	
	
	public void init(String filePath) throws IOException {
		
		InputStream is = new FileInputStream(new File(filePath));
		HSSFWorkbook wb = new HSSFWorkbook(is);
		
		Sheet dataSheet = wb.getSheetAt(0);
		try {
			int i = -1;
			while(true) {
				i++;
				if(i > DateUtils.MAX_LINE) {
					break;
				}
				Row row = dataSheet.getRow(i);
				if(row == null){
					continue;
				}
				Cell cell = row.getCell(4);
				if(cell == null) {
					continue;
				}
				//
				Cell cellTemp = row.getCell(7);
				String nameString = cell.getStringCellValue();
				String name = "";
				if(nameString == null || !nameString.contains("姓名")) {
					continue;
				}
				name = nameString.split("\\：")[1];				// 获取到姓名
				Map<String, String[]> mapMap = map.get(name);
				if(mapMap == null) {
					map.put(name, new TreeMap<String, String[]>());
					mapMap = map.get(name);
				}
				System.out.println(name);
				System.out.println("------------------"+cellTemp.getStringCellValue());
				// 向下五行开始打卡记录
				row = dataSheet.getRow(i + 5);
				Cell cellDate1 = row.getCell(0);
				
				String[] beginDate = cellDate1.getStringCellValue().split("\\.");
				daysOfMonth = DateUtils.getInstance()
										.getDaysOfMonth(beginDate[0]);
				
				Cell cellDate2 = row.getCell(8);
				
				String[] secondDate = cellDate2.getStringCellValue().split("\\.");
				int secondBegin = Integer.parseInt(secondDate[1]);
				
				int leftCount = secondBegin - 1;
				int rightCount = daysOfMonth - secondBegin + 1;
					
				System.out.println(leftCount + " : " + rightCount);
				parseTimeCardMap(dataSheet, i + 5, leftCount, 0, mapMap);
				parseTimeCardMap(dataSheet, i + 5, rightCount, 8, mapMap);
				System.out.println(map);
				i = i + 5 + leftCount;
				System.out.println(i + ":" + leftCount);
			}
			
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
	}
	
	public void parseTimeCardMap(Sheet sheet, int rowNum, int count, int leftOffset, Map<String, String[]> map) {
		for(int i = 0; i < count; i++) {
			Row row = sheet.getRow(rowNum + i);
			Cell cellDate1 = row.getCell(0 + leftOffset);
			Cell cellWeek1 = row.getCell(1 + leftOffset);
			Cell cellTimeU1 = row.getCell(2 + leftOffset);
			Cell cellTimeD1 = row.getCell(4 + leftOffset);
			if(cellWeek1 != null) {
				String week1 = cellWeek1.getStringCellValue();
				String key1 = cellDate1.getStringCellValue();
				dateWeekMap.put(key1.trim(), week1.trim());
				if(!week1.equals("六") && !week1.equals("日")) {
					dateWeekMap.put(key1.trim(), week1.trim());
					String[] sstr1 = null;
					// 初始化
					sstr1 = map.get(key1);
					if(sstr1 == null) {
						sstr1 = new String[2];
						map.put(key1, sstr1);
					}
					
					System.out.println(Arrays.asList(map.get(key1)) + ":" + Arrays.asList(sstr1));
					// 判断，如果有已经打卡的记录，并且没有迟到，那么丢弃当前数据，否则使用当前数据
					int stateU = DateUtils.getInstance().isInTime(sstr1[0], true);
					if(stateU != DateUtils.STATE_INTIME && cellTimeU1 != null) {
						sstr1[0] = cellTimeU1.getStringCellValue();
					}
					int stateD = DateUtils.getInstance().isInTime(sstr1[1], false);
					if(stateD != DateUtils.STATE_INTIME && cellTimeD1 != null) {
						sstr1[1] = cellTimeD1.getStringCellValue();
					}
				}
			}
		}
	}

	public void output(String templatePath, String descPath, Map<String, Map<String, String[]>> result) throws IOException {
		if (result == null || result.size() == 0) {
			return;
		}
		InputStream is = new FileInputStream(new File(templatePath));
		HSSFWorkbook wb = new HSSFWorkbook(is);
		
		styleTitle = getContentCellStyle(wb, HSSFColorPredefined.LIME.getIndex());
		styleLate = getContentCellStyle(wb, HSSFColorPredefined.RED.getIndex());
		styleKuang = getContentCellStyle(wb, HSSFColorPredefined.LIGHT_YELLOW.getIndex());
		styleCommon = getContentCellStyle(wb, null);
		
		outputSum(wb, map);
		
		FileOutputStream os = new FileOutputStream(descPath);
		try {
			wb.write(os);
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			if (os != null) {
				os.close();
			}
			if (wb != null) {
				wb.close();
			}
		}
	}

	private void outputSum(HSSFWorkbook wb, Map<String, Map<String, String[]>> result) {
		if (result == null || result.size() == 0) {
			return;
		}
		Sheet sheet = wb.getSheetAt(0);
        sheet.setVerticallyCenter(true);	// 垂直剧中
        sheet.createFreezePane(1,3,1,3);	// 冻结单元格
        
		Row rowTitle = sheet.createRow(1);
		Row rowTitleWeek = sheet.createRow(2);
		int titleCellIndex = 1;
		for(Entry<String, String> entry : dateWeekMap.entrySet()) {
			Cell cellTitle = rowTitle.createCell(titleCellIndex);
			cellTitle.setCellValue(entry.getKey());
			cellTitle.setCellStyle(styleTitle);
			sheet.addMergedRegion(
					new CellRangeAddress(1, 1, titleCellIndex, titleCellIndex+1));
			rowTitle.createCell(titleCellIndex + 1).setCellStyle(styleTitle);
			
			Cell cellTitleWeek = rowTitleWeek.createCell(titleCellIndex);
			cellTitleWeek.setCellValue("(星期" + entry.getValue() + ")");
			cellTitleWeek.setCellStyle(styleTitle);
			sheet.addMergedRegion(
					new CellRangeAddress(2, 2, titleCellIndex, titleCellIndex+1));
			rowTitleWeek.createCell(titleCellIndex + 1).setCellStyle(styleTitle);
			
			titleCellIndex += 2;
		}
		
		
		int rowIndex = 3;
		for (Entry<String, Map<String,String[]>> entry : result.entrySet()) {
			String name = entry.getKey();
			Row row = sheet.createRow(rowIndex);
			Cell cellName = row.createCell(0);
			cellName.setCellValue(name);
			cellName.setCellStyle(styleTitle);
			
			logger.info("--------> name = " + name);
			int cellIndex = 1;
			/////////////////////////////////////// 处理格子的样式  ///////////////////////
			for(int i = 1; i <= daysOfMonth; i++) {
				String[] daka = entry.getValue().get(rowTitle.getCell(cellIndex).getStringCellValue());
				if(daka != null) {
					Cell cellMorning = row.createCell(cellIndex);
					cellMorning.setCellValue(daka[0]);
					int state = DateUtils.getInstance().isInTime(daka[0], true);
					if(state == DateUtils.STATE_LATE) {
						cellMorning.setCellStyle(styleLate);
					} else if(state == DateUtils.STATE_KUANG) {
						cellMorning.setCellStyle(styleKuang);
					} else {
						cellMorning.setCellStyle(styleCommon);
					}
						
					Cell cellAfternoon = row.createCell(cellIndex+1);
					cellAfternoon.setCellValue(daka[1]);
					int state1 = DateUtils.getInstance().isInTime(daka[1], false);
					if(state1 == DateUtils.STATE_LATE) {
						cellAfternoon.setCellStyle(styleLate);
					} else if(state1 == DateUtils.STATE_KUANG) {
						cellAfternoon.setCellStyle(styleKuang);
					} else {
						cellAfternoon.setCellStyle(styleCommon);
					}
				}
				cellIndex += 2;
			}
			rowIndex++;
			logger.info("-----------------------------------------");
		}

	}

	/**
	 * 需要在init方法之后调用
	 * 
	 * @param list
	 * @return
	 */
	public Map<String, Map<String, String[]>> getResult() {
		return map;
	}

	// HSSFColorPredefined.LIME.getIndex()
	private CellStyle getContentCellStyle(Workbook wb, Short color) {
		CellStyle style = wb.createCellStyle();
		Font font = wb.createFont();
		font.setBold(true);
		style.setFont(font);
        //水平居中
		style.setAlignment(HorizontalAlignment.CENTER);
        //垂直居中
		style.setVerticalAlignment(VerticalAlignment.CENTER);
		style.setWrapText(true); 
		
		if(color != null) {
			style.setFillForegroundColor(color);
			style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
		}
		
		style.setBorderBottom(BorderStyle.THIN); //下边框
		style.setBorderLeft(BorderStyle.THIN);//左边框
		style.setBorderTop(BorderStyle.THIN);//上边框
		style.setBorderRight(BorderStyle.THIN);//右边框
		
		return style;
	}

}
